package com.elock.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.elock.base.core.Page;
import com.elock.base.core.Pageable;
import com.elock.base.core.dao.impl.DaoCenter;
import com.elock.base.core.util.ConvertUtil;
import com.elock.base.core.util.ExceptionUtil;
import com.elock.base.core.util.WebUtils;
import com.elock.entity.Terminal;

/**
 * Dao - 终端设备
 * @author 仁德
 */
@Repository("terminalDao")
public class TerminalDao extends DaoCenter {

//	@Resource(name = "saleOrgBaseServiceImpl")
//	private SaleOrgBaseService saleOrgBaseService;

	public Page<Map<String, Object>> findPage(String terminalNum, String terminalName,
			Long saleOrgId, String sim, Pageable pageable) {

		List<Object> list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append(
				"select t.*,so.name sale_org_name,sdt.value type_value,sdm.value model_value from terminal t");
		sql.append(" left join terminal_sale_org tso on t.id=tso.terminal_id");
		sql.append(" left join sale_org so on so.id=tso.sale_org_id");
		sql.append(" left join system_dict sdt on t.type=sdt.id");
		sql.append(" left join system_dict sdm on t.model= sdm.id where 1=1");
		if (companyInfoId != null) {
			sql.append(" and t.company_info_id=?");
			list.add(companyInfoId);
		}
		if (!ConvertUtil.isEmpty(terminalNum)) {
			sql.append(" and t.terminal_num like ?");
			list.add("%" + terminalNum + "%");
		}
		if (!ConvertUtil.isEmpty(terminalName)) {
			sql.append(" and t.terminal_name like ?");
			list.add("%" + terminalName + "%");
		}
		if (!ConvertUtil.isEmpty(sim)) {
			sql.append(" and t.sim like ?");
			list.add("%" + sim + "%");
		}
		if (saleOrgId != null) {
			sql.append(" and so.id=?");
			list.add(saleOrgId);
		}
//		sql.append(" and so.id in (" + saleOrgBaseService.saleOrgAuth() + ")");
		sql.append(" order by t.create_date desc");

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		Page<Map<String, Object>> getPage = getNativeDao().findPageMap(sql.toString(),
				objs,
				pageable);

		return getPage;

	}

	public List<Map<String, Object>> findTerminalElectronicFenceByTerminalId(
			Long terminalId) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				"select ef.*,so.name sale_org_name from terminal_electronic_fence tef");
		sql.append(" left join electronic_fence ef on tef.electronic_fence_id=ef.id");
		sql.append(
				" left join electronic_fence_sale_org efso on ef.id=efso.electronic_fence_id");
		sql.append(
				" left join sale_org so on so.id=efso.sale_org_id where tef.terminal_id="
						+ terminalId);
		sql.append(" order by tef.create_date desc");

		List<Map<String, Object>> maps = getNativeDao().findListMap(sql.toString(),
				null,
				0);

		return maps;
	}

	public Terminal findByCarNumber(String terminalNum) {

		if (ConvertUtil.isEmpty(terminalNum)) {
			ExceptionUtil.throwDaoException("value is empty!");
		}
		String sql = "select * from terminal where terminal_num = ?";
		Terminal terminal = getNativeDao().findSingleManaged(sql,
				new Object[] { terminalNum },
				Terminal.class);
		return terminal;
	}

	public Page<Map<String, Object>> findTerminalMonitorPage(String terminalNum,
			String sim, Long saleOrgId, String startTime, String endTime,
			Pageable pageable) {

		StringBuilder sql = new StringBuilder();
		List<Object> list = new ArrayList<Object>();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append(
				"select r.*,o.is_online,o.online_time,o.modify_date on_off_date,t.sim");
		sql.append(
				" from terminal_online o,terminal_realtime_position r,terminal t,terminal_sale_org tso ");
		sql.append(
				" where o.terminal_id=r.terminal_id and t.id=o.terminal_id and t.id=tso.terminal_id");
		if (companyInfoId != null) {
			sql.append(" and t.company_info_id=?");
			list.add(companyInfoId);
		}
		if (saleOrgId != null) {
			sql.append(" and tso.sale_org_id=?");
			list.add(saleOrgId);
		}
		if (!ConvertUtil.isEmpty(terminalNum)) {
			sql.append(" and r.terminal_num like ?");
			list.add("%" + terminalNum + "%");
		}
		if (!ConvertUtil.isEmpty(sim)) {
			sql.append(" and t.sim like ?");
			list.add("%" + sim + "%");
		}
		if (!ConvertUtil.isEmpty(startTime)) {
			sql.append(" and r.create_date >= ?");
			list.add(startTime + " 00:00:00");
		}
		if (!ConvertUtil.isEmpty(endTime)) {
			sql.append(" and r.create_date < ?");
			list.add(endTime + " 24:00:00");
		}

		sql.append(" order by r.modify_date desc");
		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}
		Page<Map<String, Object>> page = getNativeDao().findPageMap(sql.toString(),
				objs,
				pageable);

		return page;
	}

	public Integer countTerminalMonitor(String terminalNum, String sim, Long saleOrgId,
			String startTime, String endTime, Pageable pageable, Integer page,
			Integer size) {

		StringBuilder sql = new StringBuilder();
		List<Object> list = new ArrayList<Object>();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append("select count(r.id)");
		sql.append(
				" from terminal_online o,terminal_realtime_position r,terminal t,terminal_sale_org tso ");
		sql.append(
				" where o.terminal_id=r.terminal_id and t.id=o.terminal_id and t.id=tso.terminal_id");
		if (companyInfoId != null) {
			sql.append(" and t.company_info_id=?");
			list.add(companyInfoId);
		}
		if (saleOrgId != null) {
			sql.append(" and tso.sale_org_id=?");
			list.add(saleOrgId);
		}
		if (!ConvertUtil.isEmpty(terminalNum)) {
			sql.append(" and r.terminal_num like ?");
			list.add("%" + terminalNum + "%");
		}
		if (!ConvertUtil.isEmpty(sim)) {
			sql.append(" and t.sim like ?");
			list.add("%" + sim + "%");
		}
		if (!ConvertUtil.isEmpty(startTime)) {
			sql.append(" and r.create_date >= ?");
			list.add(startTime + " 00:00:00");
		}
		if (!ConvertUtil.isEmpty(endTime)) {
			sql.append(" and r.create_date < ?");
			list.add(endTime + " 24:00:00");
		}

		sql.append(" order by r.modify_date desc");
		if (page != null && size != null) {
			sql.append(" limit " + (size * (page - 1)) + "," + size);
		}
		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}
		Integer count = getNativeDao().findInt(sql.toString(), objs);

		return count;
	}

	public List<Map<String, Object>> findTerminalMonitorList(String terminalNum,
			String sim, Long saleOrgId, String startTime, String endTime,
			Pageable pageable, Integer page, Integer size) {

		StringBuilder sql = new StringBuilder();
		List<Object> list = new ArrayList<Object>();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append(
				"select r.*,o.is_online,o.online_time,o.modify_date on_off_date,t.sim");
		sql.append(
				" from terminal_online o,terminal_realtime_position r,terminal t,terminal_sale_org tso ");
		sql.append(
				" where o.terminal_id=r.terminal_id and t.id=o.terminal_id and t.id=tso.terminal_id");
		if (companyInfoId != null) {
			sql.append(" and t.company_info_id=?");
			list.add(companyInfoId);
		}
		if (saleOrgId != null) {
			sql.append(" and tso.sale_org_id=?");
			list.add(saleOrgId);
		}
		if (!ConvertUtil.isEmpty(terminalNum)) {
			sql.append(" and r.terminal_num like ?");
			list.add("%" + terminalNum + "%");
		}
		if (!ConvertUtil.isEmpty(sim)) {
			sql.append(" and t.sim like ?");
			list.add("%" + sim + "%");
		}
		if (!ConvertUtil.isEmpty(startTime)) {
			sql.append(" and r.create_date >= ?");
			list.add(startTime + " 00:00:00");
		}
		if (!ConvertUtil.isEmpty(endTime)) {
			sql.append(" and r.create_date < ?");
			list.add(endTime + " 24:00:00");
		}

		sql.append(" order by r.modify_date desc");
		if (page != null && size != null) {
			sql.append(" limit " + (size * (page - 1)) + "," + size);
		}

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		List<Map<String, Object>> maps = getNativeDao().findListMap(sql.toString(),
				objs,
				0);

		return maps;
	}

	public Integer countTerminalList(String terminalNum, String terminalName,
			Long saleOrgId, String sim, Pageable pageable, Integer page, Integer size) {

		List<Object> list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append("select count(1) from terminal t");
		sql.append(" left join terminal_sale_org tso on t.id=tso.terminal_id");
		sql.append(" left join sale_org so on so.id=tso.sale_org_id");
		sql.append(" left join system_dict sdt on t.type=sdt.id");
		sql.append(" left join system_dict sdm on t.model= sdm.id where 1=1");
		if (companyInfoId != null) {
			sql.append(" and t.company_info_id=?");
			list.add(companyInfoId);
		}
		if (!ConvertUtil.isEmpty(terminalNum)) {
			sql.append(" and t.terminal_num like ?");
			list.add("%" + terminalNum + "%");
		}
		if (!ConvertUtil.isEmpty(terminalName)) {
			sql.append(" and t.terminal_name like ?");
			list.add("%" + terminalName + "%");
		}
		if (!ConvertUtil.isEmpty(sim)) {
			sql.append(" and t.sim like ?");
			list.add("%" + sim + "%");
		}
		if (saleOrgId != null) {
			sql.append(" and so.id=?");
			list.add(saleOrgId);
		}
		sql.append(" order by t.create_date desc");
		if (page != null && size != null) {
			sql.append(" limit " + (size * (page - 1)) + "," + size);
		}

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		Integer count = getNativeDao().findInt(sql.toString(), objs);

		return count;
	}

	public List<Map<String, Object>> findTerminalList(String terminalNum,
			String terminalName, Long saleOrgId, String sim, Pageable pageable,
			Integer page, Integer size) {

		List<Object> list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append(
				"select t.*,so.name sale_org_name,sdt.value type_value,sdm.value model_value from terminal t");
		sql.append(" left join terminal_sale_org tso on t.id=tso.terminal_id");
		sql.append(" left join sale_org so on so.id=tso.sale_org_id");
		sql.append(" left join system_dict sdt on t.type=sdt.id");
		sql.append(" left join system_dict sdm on t.model= sdm.id where 1=1");
		if (companyInfoId != null) {
			sql.append(" and t.company_info_id=?");
			list.add(companyInfoId);
		}
		if (!ConvertUtil.isEmpty(terminalNum)) {
			sql.append(" and t.terminal_num like ?");
			list.add("%" + terminalNum + "%");
		}
		if (!ConvertUtil.isEmpty(terminalName)) {
			sql.append(" and t.terminal_name like ?");
			list.add("%" + terminalName + "%");
		}
		if (!ConvertUtil.isEmpty(sim)) {
			sql.append(" and t.sim like ?");
			list.add("%" + sim + "%");
		}
		if (saleOrgId != null) {
			sql.append(" and so.id=?");
			list.add(saleOrgId);
		}
		sql.append(" order by t.create_date desc");
		if (page != null && size != null) {
			sql.append(" limit " + (size * (page - 1)) + "," + size);
		}

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		List<Map<String, Object>> maps = getNativeDao().findListMap(sql.toString(),
				objs,
				0);

		return maps;
	}
}